import random
from utils import XLUtils
import pymysql
import openpyxl

#随机生成数据
def data_create(id):
    family_name = ['张','李','冯','王','郑','许','朱','姜','魏','楚','吴','周','孙','赵','王','胡','何','施']
    name = ['清','欢乐','梦','清梦','星河','在水','舟','徐来','央','画','形','风','礼','树子','芳华'
            ,'桂','梅','桃','杏','水','烟','言']
    nickname = ['小清','小欢','小梦','清梦','星河','在水','舟','徐来','央','画','形','风','礼','树子','芳华','桂梅','桃杏','水烟','言语',
                '水','火','金','王','地虎','熊猫']


    list1 = [id]
    idcard = str(id + random.randint(100000,999999))
    list1.append(idcard)
    username = name[random.randint(0,len(name)-1)] + nickname[random.randint(0,len(nickname)-1)]
    list1.append(username)
    realname = family_name[random.randint(0,len(family_name)-1)] + name[random.randint(0,len(name)-1)]
    list1.append(realname)
    pwd = '123456'
    list1.append(pwd)
    telphone = '138' + str(random.randint(10000000,99999999))
    list1.append(telphone)
    email = username + '@163.com'
    list1.append(email)
    age = random.randint(18,60)
    list1.append(age)
    sex = random.choice(['男','女'])
    list1.append(sex)
    address = '北京市' + str(random.randint(100,1000)) + '区' + str(random.randint(100,1000)) + '号'
    list1.append(address)
    hiredate = '2021-0' + str(random.randint(1,9)) + '-0' + str(random.randint(1,9))
    list1.append(hiredate)
    sal = round(random.uniform(0,1000000),2)
    list1.append(sal)
    job = random.choice(['销售','经理','技术','产品','人事','行政','财务','后勤','客服','采购','质量','工程','法务','行政','人力资源','其他'])
    list1.append(job)
    company = random.choice(['华为','腾讯','阿里巴巴','百度','京东','网易','美团','滴滴','其他'])
    list1.append(company)
    return list1

#创建xlsx文件

def create_xlsx(file_name):
    wb = openpyxl.Workbook(file_name)
    wb.save('user_info.xlsx')
    wb.close()

def insert_data(file_name,sheet_name):
    user_info = openpyxl.load_workbook(file_name)
    if sheet_name not in user_info.sheetnames:
        sheet1 = user_info.create_sheet(sheet_name)
    else:
        sheet1 = user_info[sheet_name]
    sheet1.append(['id','idcard','username','realname','pwd','telphone','email','age','sex','address','hiredate','sal','job','company'])
    #写入2000行10列数据
    for row in range(1,2001):
        sheet1.append(data_create(row))

    user_info.save('user_info.xlsx')
    user_info.close()
#插入数据
# insert_data('user_info.xlsx','user_info')
#读取xlsx文件
result = XLUtils.XLUtils.read_excel('user_info.xlsx','user_info')
# for row in result:
#     for cell in row:
#         print(cell,end='\t')
#     print()

#插如Mysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,database='hrtest',charset='utf8')
sql = "insert into user_info(id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,company) " \
      "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
    with db.cursor() as cursor:
        cursor.executemany(sql,result)
        db.commit()
except Exception as e:
    print("插如出错",e)
    db.rollback()
finally:
    cursor.close()
    db.close()


